Overview:

PyMySQL is a MySQL client library written using the Python Programming Language.

PyMySQL is used to connect to a MySQL server and perform database operations from a Python program.

PyMySQL works with the following Python implementations
- CPython
- PyPy
- IronPython

This article describes how to delete a set of rows from a MySQL database table.

Steps involved in deleting a set of rows from a MySQL Table:

The SQL statement DELETE is a DML statement. DML stands for data Manipulation Language.

Unless a filter using WHERE clause is specified the DELETE statement will delete all the rows from a specified table.

Deleting a set of rows from a MySQL table involves:

Creation of a database connection by specifying the database server using IP or host name along with database user credentials
Obtaining a cursor instance from the database connection
Defining a string containing the SQL command to delete a set of rows including the WHERE clause if required
Calling the execute() method on cursor by passing the DELETE SQL string as the parameter

In the python example given here, the autocommit is made True while creating the database connection. The remaining rows from the table are printed using the SQL SELECT statement.

Example:

# import the pymysql module

import pymysql

# Code for creating a connection object

databaseServer = "127.0.0.1"

databaseUser = "root"

databaseUserPwd = ""

databaseName = "test"

databaseCharSet = "utf8mb4"

dbConnection = pymysql.connect(host=databaseServer,

user=databaseUser,

password=databaseUserPwd,

db=databaseName,

charset=databaseCharSet,

autocommit=True)

try:

# Code for creating cursor from database connection

cursorInstance = dbConnection.cursor()

# SQL statement for deleting rows from a table matching a criteria

sqlDeleteRows = "Delete from tbl_items where status='expired'"

# using the cursor delete a set of rows from the table

cursorInstance.execute(sqlDeleteRows)

# Check if there are any existing items with expired status

sqlSelectRows = "select * from tbl_items"

# Execute the SQL query

cursorInstance.execute(sqlSelectRows)

#Fetch all the rows using cursor object

itemRows = cursorInstance.fetchall()

# print all the remaining rows after deleting the rows with status as "expired"

for item in itemRows:

print(item)

except Exception as ex:

print("Exception occured: %s"%ex)

finally:

dbConnection.close()

Output:

(1, 'butter', 50, 'fresh')

(2, 'milk', 200, 'fresh')

(3, 'yogurt', 100, 'fresh')

Deleting Rows from a MySQL Database Table Using Python And PyMySQL

Overview:

Steps involved in deleting a set of rows from a MySQL Table:

Example:

Output: